package reaction.news.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import reaction.news.index.NewsArticle;

public class Queries {
	
	public static List<NewsArticle> getNewsArticles() throws Exception, SQLException {
		
		List<NewsArticle> news = new LinkedList<NewsArticle>();
		
		// Connect to BD
		Connection connect = MySQLConnection.getConnection("publico", "publ1c0","");
				
		// Statements allow to issue SQL queries to the database
		Statement statement = connect.createStatement();
		
		// Result set get the result of the SQL query
		ResultSet resultSet = statement.executeQuery("SELECT id, category_id, date, title_rembrandted, subtitle_rembrandted, newstext_rembrandted FROM politica LIMIT 10");
		
		while (resultSet.next()) {
			NewsArticle article = new NewsArticle(resultSet.getInt("id"), resultSet.getInt("category_id"),
					resultSet.getString("title_rembrandted"),resultSet.getString("subtitle_rembrandted"),resultSet.getString("newstext_rembrandted"));
			news.add(article);
		}		
		
		connect.close();
		
		return news;
	}
	
	public static List<NewsArticle> getNewsIDs() throws Exception, SQLException {
		
		List<NewsArticle> news = new LinkedList<NewsArticle>();
		
		// Connect to BD
		Connection connect = MySQLConnection.getConnection("publico", "publ1c0","");
				
		// Statements allow to issue SQL queries to the database
		Statement statement = connect.createStatement();
		
		// Result set get the result of the SQL query
		ResultSet resultSet = statement.executeQuery("SELECT id, category_id FROM politica LIMIT 10");
		
		while (resultSet.next()) {
			NewsArticle article = new NewsArticle(resultSet.getInt("id"), resultSet.getInt("category_id"));
			news.add(article);
		}		
		
		connect.close();
		
		return news;
	}
}
